package org.mdrtwell.common.excel2db.service.support;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BlankCell;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.mdrtwell.common.excel2db.service.DbConnectionException;
import org.mdrtwell.common.excel2db.service.ExcelException;
import org.mdrtwell.common.excel2db.service.LoadConfigException;
import org.xml.sax.SAXException;

public class ExcelDataService implements org.mdrtwell.common.excel2db.service.IExcelDataService {
	private Document		document;
	private List<String>	sqls	= new ArrayList<String>();

	/**
	 * @param mappingFileFullPath
	 *            ӳ���ļ�ȫ·��
	 * @throws LoadConfigException
	 */
	public ExcelDataService(String mappingFileFullPath) throws LoadConfigException {
		URL url = ExcelDataService.class.getResource("/com/cattsoft/common/excel2db/xls2db-config.xsd");
		document = load(mappingFileFullPath, url);
	}

	/**
	 * װ��ӳ���ļ�
	 * 
	 * @param xmlFile
	 * @param schema
	 * @return
	 * @throws LoadConfigException
	 * @throws SAXException
	 * @throws DocumentException
	 */
	private Document load(String xmlFile, URL schema) throws LoadConfigException {
		Document doc = null;
		SAXReader reader = new SAXReader(true);
		try {
			reader.setFeature("http://xml.org/sax/features/validation", true);
			reader.setFeature("http://apache.org/xml/features/validation/schema", true);
			reader.setFeature("http://apache.org/xml/features/validation/schema-full-checking",
					true);
			reader.setProperty("http://apache.org/xml/properties/schema/external-noNamespaceSchemaLocation",
					schema.toString());
			doc = reader.read(new File(xmlFile));
		} catch (SAXException e) {
			e.printStackTrace();
			throw new LoadConfigException("װ�������ļ����?");
		} catch (DocumentException e) {
			e.printStackTrace();
			throw new LoadConfigException("װ�������ļ����?");
		}
		return doc;
	}

	/**
	 * ��ʽ������
	 * 
	 * @param date
	 * @return
	 */
	private String formatDate(Date date) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		return dateFormat.format(date);
	}

	/**
	 * ����Excel����
	 * 
	 * @param filepath
	 * @throws ExcelException
	 */
	public void execute(String filepath) throws ExcelException {
		Workbook workbook;
		try {
			workbook = Workbook.getWorkbook(new File(filepath));
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelException("��ȡExcel���ݳ��?");
		}
		// Ĭ����Ϊ��ݴ���ڵ�һ����ݱ��У����ڻ���֧�ֶ�sheet����
		Sheet sheet = workbook.getSheet(0);
		// ���Excel����ļ���
		String level = getExcelTitleLevel();
		// ѭ���������ÿһ�У�ƴ�ӳ�sql��䣬�������
		for (int i = 0; i < sheet.getRows(); i++) {
			// ���⣬������ӳ��
			if (i < new Integer(level))
				continue;
			// һ��map��Ӧһ��sql��䣬�����洢sql�ֶ�ֵӳ��
			Map<String, String> map = new HashMap<String, String>();
			// �õ���ǰ�е����е�Ԫ��
			Cell[] cells = sheet.getRow(i);
			for (int j = 0; j < cells.length; j++) {
				Cell cell = cells[j];
				String content = null;
				if (cell.getType() == CellType.DATE) {
					DateCell dateCell = (DateCell) cell;
					content = formatDate(dateCell.getDate());
				} else {
					content = cell.getContents();
				}
				// ��ȡ��Ԫ���Ӧ�ı���
				String title = getTitle(sheet, cell);
				// ��ݱ���ȷ�������ݿ��ֶ���
				String fldName = getDBFieldName(title);
				map.put(fldName, content);
			}
			storeSql(map);
		}
		storeTable(sqls);
		workbook.close();
	}

	/**
	 * ��ô�Excel������ı�ͷ���� ���磺3����������ͷ
	 * 
	 * @return
	 */
	private String getExcelTitleLevel() {
		String levelXpath = "/mapping/depth";
		Element e = (Element) document.selectSingleNode(levelXpath);
		String level = e.getStringValue();
		return level;
	}

	/**
	 * ���ĳ����Ԫ���Ӧ�ı���
	 * 
	 * @param sheet
	 * @param cell
	 * @return
	 */
	private String getTitle(Sheet sheet, Cell cell) {
		String title = null;
		for (Cell tempCell = sheet.getCell(cell.getColumn(), 0); title == null; tempCell = sheet.getCell(cell.getColumn(),
				tempCell.getRow() + 1)) {
			if (tempCell instanceof BlankCell || validateHasSubTitle(tempCell.getContents())
					|| tempCell.getContents().equals(""))
				continue;
			title = tempCell.getContents();
		}
		return title;
	}

	/**
	 * ��֤�˱����Ƿ����ӱ���
	 * 
	 * @param title
	 * @return
	 */
	private boolean validateHasSubTitle(String title) {
		String xpath = "/mapping/map[@supertitle='" + title + "']";
		Element e = (Element) document.selectSingleNode(xpath);
		return e != null;
	}

	/**
	 * ��sql���д洢
	 * 
	 * @param map
	 */
	private void storeSql(Map<String, String> map) {
		Map<String, Map> groupedMap = groupingMap(map);
		orgnizeSqlAndStore(groupedMap);
	}

	/**
	 * ��sql map���з���
	 * 
	 * @param map
	 * @return
	 */
	private Map groupingMap(Map map) {
		Map<String, Map> groupedMap = new HashMap<String, Map>();
		for (Iterator iterator = map.entrySet().iterator(); iterator.hasNext();) {
			Map.Entry entry = (Map.Entry) iterator.next();
			String fldName = (String) entry.getKey();
			String value = (String) entry.getValue();
			String tableName = getFldTableName(fldName);
			Map tempMap = groupedMap.get(tableName);
			if (tempMap == null) {
				Map m = new HashMap();
				m.put(fldName, value);
				groupedMap.put(tableName, m);
			} else {
				tempMap.put(fldName, value);
			}
		}
		return groupedMap;
	}

	/**
	 * ���ĳ�ֶζ�Ӧ�ı���
	 * 
	 * @param fldName
	 * @return
	 */
	private String getFldTableName(String fldName) {
		String tableName = null;
		String xpath = "/mapping/map[@dbfieldname='" + fldName + "']/@dbtablename";
		Node node = document.selectSingleNode(xpath);
		if (node != null) {
			tableName = node.getText();
		}
		return tableName;
	}

	/**
	 * ����ݱ��浽��ݿ�
	 * 
	 * @param list
	 * @throws ExcelException
	 */
	private void storeTable(List<String> list) throws ExcelException {
		Connection connection = null;
		Statement stmt = null;
		try {
			connection = getConnection();
			connection.setAutoCommit(false);
			stmt = connection.createStatement();
			for (String sql : list) {
				stmt.addBatch(sql);
			}
			stmt.executeBatch();
			connection.commit();
			stmt.close();
			connection.close();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExcelException(e.getMessage());
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * �����ݿ�����
	 * 
	 * @return
	 * @throws DbConnectionException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private Connection getConnection() throws DbConnectionException {
		Connection conn;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@10.21.3.203:1521:datadb";
			String username = "maxutest";
			String pwd = "maxutest";
			conn = DriverManager.getConnection(url, username, pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new DbConnectionException("��ȡ��ݿ����ӳ��?");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DbConnectionException("��ȡ��ݿ����ӳ��?");
		}
		return conn;
	}

	/**
	 * ��֯������ݿ��sql���
	 * 
	 * @param map
	 * @return
	 */
	private String orgnizeSql(Map<String, String> map, String tableName) {
		StringBuilder sql = new StringBuilder();
		StringBuilder fields = new StringBuilder("ID");
		StringBuilder values = new StringBuilder(getSequenceName() + ".NEXTVAL");
		for (Iterator it = map.keySet().iterator(); it.hasNext();) {
			String fldName = (String) it.next();
			fields.append("," + fldName);
			String fldValue = map.get(fldName);
			String fldType = getDBFldType(fldName);
			if (fldType != null && fldType.equalsIgnoreCase("Date")) {
				values.append(",To_date('" + fldValue + "','yyyy.MM.dd')");
			} else {
				values.append(",'" + fldValue + "'");
			}
		}
		sql.append("insert into ");
		sql.append(tableName);
		sql.append(" (");
		sql.append(fields);
		sql.append(") values (");
		sql.append(values);
		sql.append(")");
		return sql.toString();
	}

	/**
	 * ���ѷ����map���sql��䲢���б���
	 * 
	 * @param groupedMap
	 */
	private void orgnizeSqlAndStore(Map<String, Map> groupedMap) {
		for (Iterator it = groupedMap.keySet().iterator(); it.hasNext();) {
			String tableName = (String) it.next();
			Map target = groupedMap.get(tableName);
			String sql = orgnizeSql(target, tableName);
			sqls.add(sql);
		}
	}

	/**
	 * �õ���ݿ�������
	 * 
	 * @return
	 */
	private String getSequenceName() {
		String seqXpath = "/mapping/seqname";
		Element e = (Element) document.selectSingleNode(seqXpath);
		String seqName = e.getText();
		return seqName;
	}

	/**
	 * ��ȡ�ֶε��������
	 * 
	 * @param fldName
	 * @return
	 */
	private String getDBFldType(String fldName) {
		String fldType = null;
		String xpath = "/mapping/map[@dbfieldname='" + fldName + "']/@dbfieldtype";
		Node node = document.selectSingleNode(xpath);
		if (node != null) {
			fldType = node.getText();
		}
		return fldType;
	}

	/**
	 * ��ȡExcel�����Ӧ����ݿ��ֶ���
	 * 
	 * @param title
	 * @return
	 */
	private String getDBFieldName(String title) {
		String xpath = "/mapping/map[@title='" + title + "']/@dbfieldname";
		String fldName = document.valueOf(xpath);
		return fldName;
	}
}
